SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_BANC_Q06]
/*-------------------------------------------------------*/
/*---Empresa               OFISIS S.A.                --*/
/*---Sistema               Tesoreria                  --*/
/*---M®dulo                Empresa                    --*/
/*---Programa              FLUJO PROYECTADO MENSUAL   --*/
/*---Script                SP_TCMOVI_BANC_Q06.sql     --*/
/*---Nombre SP             SP_TCMOVI_BANC_Q06         --*/
/*---Desarrollado por      Eduardo Vivar Pomiano      --*/
/*---Fecha Creaci®n        11/10/2001                 --*/
/*---Base Datos            SQL SERVER 7		      --*/
/*---Versi®n               	                      --*/
/*---Invoca a SP                                      --*/
/*------------------------------------------------------*/
/*------------------------------------------------------*/
/*---Modificado 1 por     Carlos Marquez Escobar	--*/
/*---Fecha Modificaci®n   05/04/2005		 	--*/ 
/*---Detalle Modificaci®n Standarizaci½n		--*/
/*------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Drop Proc SP_TCMOVI_BANC_Q06                      --*/
/*---Grant all on SP_TCMOVI_BANC_Q06 to public         --*/ 
/*---SP_TCMOVI_BANC_Q06 '01', 2005, 4, 'U'  		--*/
/*-------------------------------------------------------*/

@ISCO_EMPR 	TD_VC_002,
@INNU_ANIO	TD_IN_001,
@INNU_MESE	TD_IN_001,
@ISTI_PRES	TD_VC_001--- COMO MOSTRAR LOS IMPORTES 'U' =UNIDADES, 'M' =MILES
 
AS	

CREATE TABLE #TWFLUJ_CAJA_Q02_1
 ( CO_EMPR      varchar(3) NULL,
   CO_ARUB	varchar(3) NULL ,
   CO_RUBR	varchar(3) NULL ,
   CO_SUBR	varchar(3) NULL ,
   DE_ARUB	varchar(80) NULL ,
   DE_RUBR	varchar(80) NULL ,
   DE_SUBR	varchar(80) NULL ,
   FA_TIPO_CAMB numeric(9,4) DEFAULT 0,
   CO_MONE	varchar(3) NULL,
   FE_OPER	datetime NULL,
   IM_0001_NACI	numeric(15,4) DEFAULT 0,
   IM_0001_EXTR	numeric(15,4) DEFAULT 0,
   IM_0002_NACI	numeric(15,4) DEFAULT 0,
   IM_0002_EXTR	numeric(15,4) DEFAULT 0,
   IM_0003_NACI	numeric(15,4) DEFAULT 0,
   IM_0003_EXTR	numeric(15,4) DEFAULT 0,
   IM_0004_NACI	numeric(15,4) DEFAULT 0,
   IM_0004_EXTR	numeric(15,4) DEFAULT 0,
   IM_0005_NACI	numeric(15,4) DEFAULT 0,
   IM_0005_EXTR	numeric(15,4) DEFAULT 0,
   IM_0006_NACI	numeric(15,4) DEFAULT 0,
   IM_0006_EXTR	numeric(15,4) DEFAULT 0,
   IM_0007_NACI	numeric(15,4) DEFAULT 0,
   IM_0007_EXTR	numeric(15,4) DEFAULT 0,
   IM_SAIN_NACI	numeric(15,4) DEFAULT 0,
   IM_SAIN_EXTR	numeric(15,4) DEFAULT 0)



DECLARE
@VNFE_0001		TD_IN_001, 
@VNNU_MESE		TD_IN_001,
@VNNU_ANNO		TD_IN_001,
@VNNU_CONT_MESE	TD_IN_001,
@VSDE_SELE		VARCHAR(500),
@CSCO_EMPR		VARCHAR (50),
@VSDE_CAMP		VARCHAR(7),
@VSNU_ANIO		VARCHAR(4),
@VSNU_MESE		VARCHAR (2),
@VNNU_FACT		NUMERIC(16,4),
@OSTI_DOCU_LETR	TD_VC_003,
@VNNU_MESE_ANTE	TD_IN_001,
@VNNU_ANNO_ANTE	TD_IN_001,
@VSCO_MONE_NACI	TD_VC_003,
@VSCO_MONE_EXTR	TD_VC_003,
@VDFE_INIC 		TD_DT_001,
@VNIM_EMPL	NUMERIC(16,4),
@VNIM_OBRE	NUMERIC(16,4),

@VNIM_0001	NUMERIC(16,4),
@VNIM_0002	NUMERIC(16,4),
@VNIM_0003	NUMERIC(16,4),
@VNIM_0004	NUMERIC(16,4),
@VNIM_0005	NUMERIC(16,4),
@VNIM_0006	NUMERIC(16,4),
@VNIM_0007	NUMERIC(16,4),

@VNIM_AFPS	NUMERIC(16,4),
@VNIM_CTSM	NUMERIC(16,4),
@VNIM_CTSS	NUMERIC(16,4),
@VNIM_SENA	NUMERIC(16,4),
@VNIM_ESSA	NUMERIC(16,4),
@VNIM_IESS	NUMERIC(16,4),
@VNIM_QCAT	NUMERIC(16,4),
@VDFE_INIC_AUXI TD_DT_001,

@VSCO_ARUB_INGR	TD_VC_003,
@VSCO_ARUB_EGRE	TD_VC_003,
@VSDE_ARUB_INGR	TD_VC_100,
@VSDE_ARUB_EGRE	TD_VC_100



-- PRESENTACION EN UNIDADES O MILES
IF @ISTI_PRES = 'U'
 SELECT @VNNU_FACT = 1
IF @ISTI_PRES = 'M'
 SELECT @VNNU_FACT = 0.001

-- PERIODO A¾O-MES

SELECT 	@VSNU_MESE = Convert (Varchar(02),@INNU_MESE), 
		@VSNU_ANIO = Convert (Varchar(04),@INNU_ANIO)

SELECT  @VDFE_INIC = Convert(Datetime,'01'+'/'+@VSNU_MESE+'/'+@VSNU_ANIO, 103)


-- Soles y Dolares
Select	@VSCO_MONE_NACI = CO_MONE_DEFA, @VSCO_MONE_EXTR = CO_MONE_EXTR
From 	TMPARA_TESO
Where	CO_EMPR = @ISCO_EMPR

-- Cual es el tipo de Doc Letras
Exec 	SP_TTDOCU_CNTB_Q02 6, @OSTI_DOCU_LETR  OUTPUT    

/****************************************************/

/** I.- INGRESOS ************************************/
select	@VSCO_ARUB_INGR = CO_ARUB,
		@VSDE_ARUB_INGR = DE_ARUB
from	TTARUB_FINA
Where 	CO_EMPR = @ISCO_EMPR And
		NU_ORDE = 1

-- 1.- PROYECCION DE COBRANZAS FUTURAS
INSERT INTO #TWFLUJ_CAJA_Q02_1(CO_EMPR , CO_ARUB, DE_ARUB, CO_RUBR, DE_RUBR, CO_SUBR, DE_SUBR,
		     IM_0001_NACI, IM_0002_NACI, IM_0003_NACI, IM_0004_NACI, 
		     IM_0005_NACI, IM_0006_NACI, IM_0007_NACI, 
		     IM_0001_EXTR, IM_0002_EXTR, IM_0003_EXTR, IM_0004_EXTR, 
		     IM_0005_EXTR, IM_0006_EXTR, IM_0007_EXTR )
SELECT	T1.CO_EMPR, @VSCO_ARUB_INGR, @VSDE_ARUB_INGR, '001', 'INGRESOS X VENTAS', 
		T1.CO_TIPO_DOCU, MAX(T2.NO_DOCU),
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 0, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 1, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 2, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 3, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 4, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 5, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 6, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 0, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 1, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 2, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 3, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 4, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 5, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  )  --- Semana 6, Dolares
 FROM TMDOCU_CLIE  T1, TTDOCU_CNTB T2
 WHERE 	T1.CO_EMPR = @ISCO_EMPR
 AND T1.CO_ESTA_DOCU !=  'PAG'
 AND T1.CO_ESTA_DOCU !=  'ANU'
 AND T1.CO_ESTA_DOCU !=  'REN'
 AND T1.CO_ESTA_DOCU !=  'CNJ'
 AND T1.IM_TOTA - T1.IM_PAGA > 0
 AND T1.FE_VENC >= @VDFE_INIC
 AND DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC ) <= 6  
 AND T1.CO_TIPO_DOCU = T2.TI_DOCU
 GROUP BY T1.CO_EMPR, T1.CO_TIPO_DOCU
UNION ALL
SELECT	T1.CO_EMPR, @VSCO_ARUB_INGR, @VSDE_ARUB_INGR, '001', 'OTROS INGRESOS', 
		T2.TI_DOCU, MAX(T2.NO_DOCU),
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 0, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 1, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 2, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 3, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 4, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 5, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 6, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 0, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 1, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 2, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 3, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 4, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  ),  --- Semana 5, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'S') + PATINDEX (T2.ST_SIGN, 'N')* -1 )
		  )  --- Semana 6, Dolares
 FROM TMLETR_CLIE  T1, TTDOCU_CNTB T2
 WHERE 	T1.CO_EMPR = @ISCO_EMPR
 AND T1.CO_ESTA_DOCU !=  'PAG'
 AND T1.CO_ESTA_DOCU !=  'ANU'
 AND T1.CO_ESTA_DOCU !=  'REN'
 AND T1.CO_ESTA_DOCU !=  'CNJ'
 AND T1.IM_TOTA - T1.IM_PAGA > 0
 AND T1.FE_VENC >= @VDFE_INIC
 AND DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC ) <= 6  
 AND T2.TI_DOCU = @OSTI_DOCU_LETR
 GROUP BY T1.CO_EMPR, T2.TI_DOCU

/** II.- EGRESOS ************************************/
select	@VSCO_ARUB_EGRE = CO_ARUB,
		@VSDE_ARUB_EGRE = DE_ARUB
from	TTARUB_FINA
Where 	CO_EMPR = @ISCO_EMPR And
	NU_ORDE = 2

 -- 1.- PROYECCION DE PAGOS FUTUROS
INSERT INTO #TWFLUJ_CAJA_Q02_1(CO_EMPR , CO_ARUB, DE_ARUB, CO_RUBR, DE_RUBR, CO_SUBR, DE_SUBR,
		     IM_0001_NACI, IM_0002_NACI, IM_0003_NACI, IM_0004_NACI, 
		     IM_0005_NACI, IM_0006_NACI, IM_0007_NACI, 
		     IM_0001_EXTR, IM_0002_EXTR, IM_0003_EXTR, IM_0004_EXTR, 
		     IM_0005_EXTR, IM_0006_EXTR, IM_0007_EXTR )
SELECT	T1.CO_EMPR, @VSCO_ARUB_EGRE, @VSDE_ARUB_EGRE, '015', 'DEUDA PROVEEDORES', 
		T1.CO_TIPO_OPER, Max(T2.DE_TIPO_OPER),
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 0, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 1, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 2, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 3, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 4, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 5, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 6, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 0, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 1, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 2, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 3, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 4, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 5, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T3.ST_SIGN, 'N') + PATINDEX (T3.ST_SIGN, 'S')* -1 )
		  )  --- Semana 6, Dolares
 FROM TMDOCU_PROV  T1, TTTIPO_OPER T2 , TTDOCU_CNTB T3
 WHERE 	T1.CO_EMPR = @ISCO_EMPR
 AND T1.CO_ESTA_DOCU NOT IN ( 'PAG', 'ANU', 'REN', 'CNJ')
 AND T1.IM_TOTA - T1.IM_PAGA > 0
 AND T1.FE_VENC >= @VDFE_INIC
 AND DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC ) <= 6  
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
 AND ( T1.CO_TIPO_OPER != '998' 
 AND T1.CO_TIPO_DOCU = T3.TI_DOCU
 AND T1.CO_TIPO_DOCU NOT IN (SELECT  T3.TI_COMP_PAGO
			FROM  TCCOMP_PAGO  T3
			GROUP BY T3.TI_COMP_PAGO
			)
	)
 Group By T1.CO_EMPR, T1.CO_TIPO_OPER 
UNION ALL
SELECT  T1.CO_EMPR, @VSCO_ARUB_EGRE, @VSDE_ARUB_EGRE, '015', 'DEUDA PROVEEDORES', 
		T1.CO_TIPO_OPER, Max(T2.DE_TIPO_OPER),
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 0, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 1, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 2, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 3, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 4, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 5, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 6, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 0, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 1, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 2, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 3, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 4, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  ),  --- Semana 5, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA ) * -1
		  )  --- Semana 6, Dolares
 FROM TMLETR_PROV  T1, TTTIPO_OPER T2
 WHERE 	( ( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR ) OR @ISCO_EMPR IS NULL )	
 AND T1.CO_ESTA_DOCU NOT IN ( 'PAG', 'ANU', 'REN', 'CNJ')
 AND T1.IM_TOTA - T1.IM_PAGA > 0
 AND T1.FE_VENC >= @VDFE_INIC
 AND DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC ) <= 6 
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER
 AND ( T1.CO_TIPO_OPER != '998')
 Group By T1.CO_EMPR, T1.CO_TIPO_OPER



 -- PRESTAMOS
INSERT INTO #TWFLUJ_CAJA_Q02_1(CO_EMPR , CO_ARUB, DE_ARUB, CO_RUBR, DE_RUBR, CO_SUBR, DE_SUBR,
		     IM_0001_NACI, IM_0002_NACI, IM_0003_NACI, IM_0004_NACI, 
		     IM_0005_NACI, IM_0006_NACI, IM_0007_NACI, 
		     IM_0001_EXTR, IM_0002_EXTR, IM_0003_EXTR, IM_0004_EXTR, 
		     IM_0005_EXTR, IM_0006_EXTR, IM_0007_EXTR )
SELECT	T1.CO_EMPR, @VSCO_ARUB_EGRE, @VSDE_ARUB_EGRE, '011', 'PRESTAMOS Y/O FINANCIAMIENTO', 
		T1.CO_TIPO_DOCU, Max(T2.NO_DOCU),
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 0, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 1, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 2, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 3, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 4, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 5, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_NACI)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 6, Soles
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '0')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 0, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '1')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 1, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '2')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 2, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '3')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 3, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '4')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 4, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '5')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  ),  --- Semana 5, Dolares
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC) ), '6')*
			PATINDEX(T1.CO_MONE, @VSCO_MONE_EXTR)*( T1.IM_TOTA - T1.IM_PAGA )*
			( PATINDEX (T2.ST_SIGN, 'N') + PATINDEX (T2.ST_SIGN, 'S')* -1 )
		  )  --- Semana 6, Dolares
FROM TMDOCU_PROV  T1, TTDOCU_CNTB T2
WHERE 	T1.CO_EMPR = @ISCO_EMPR
 AND T1.CO_ESTA_DOCU NOT IN ( 'PAG', 'ANU', 'REN', 'CNJ')
 AND T1.IM_TOTA - T1.IM_PAGA > 0
 AND T1.FE_VENC >= @VDFE_INIC
 AND DATEDIFF ( MONTH, @VDFE_INIC, T1.FE_VENC ) <= 6  
 AND T1.CO_TIPO_DOCU = T2.TI_DOCU
 AND (T1.CO_TIPO_OPER = '998' 
 OR T1.CO_TIPO_DOCU IN (SELECT  T3.TI_COMP_PAGO
			FROM  TCCOMP_PAGO  T3
			GROUP BY T3.TI_COMP_PAGO
			)
	)
 Group By T1.CO_EMPR, T1.CO_TIPO_DOCU

/****************************************************/
-- Proyeccion Digitada ---
INSERT INTO #TWFLUJ_CAJA_Q02_1(CO_EMPR , CO_ARUB, DE_ARUB, CO_RUBR, DE_RUBR, CO_SUBR, DE_SUBR,
		     IM_0001_NACI, IM_0002_NACI, IM_0003_NACI, IM_0004_NACI, 
		     IM_0005_NACI, IM_0006_NACI, IM_0007_NACI, 

		     IM_0001_EXTR, IM_0002_EXTR, IM_0003_EXTR, IM_0004_EXTR, 
		     IM_0005_EXTR, IM_0006_EXTR, IM_0007_EXTR )
	Select	T1.CO_EMPR,  T1.CO_ARUB, Max(T1.DE_ARUB ), T2.CO_RUBR, Max(T2.DE_RUBR), T3.CO_SUBR, Max(T3.DE_SUBR),
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '0')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 0, Soles Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '1')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 1, Soles Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '2')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 2, Soles Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '3')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 3, Soles Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '4')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 4, Soles Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '5')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 5, Soles Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '6')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_NACI)*( T4.IM_DIGI )
		),-- Semana 6, Soles Proyectado

	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '0')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		),-- Semana 0, Dolares Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '1')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		),-- Semana 1, Dolares Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '2')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		),-- Semana 2, Dolares Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '3')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		),-- Semana 3, Dolares Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '4')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		),-- Semana 4, Dolares Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '5')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		),-- Semana 5, Dolares Proyectado
	 SUM(	PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI) ), '6')*
			PATINDEX(T4.CO_MONE, @VSCO_MONE_EXTR)*( T4.IM_DIGI )
		)-- Semana 6, Dolares Proyectado
	From	TTARUB_FINA T1, TTRUBR_FINA T2, TTSUBR_FINA T3, TDSUBR_PROY T4
	Where	T1.CO_EMPR = @ISCO_EMPR
		And T1.ST_REPO = 'S'
		And T1.CO_EMPR = T2.CO_EMPR
		And T1.CO_ARUB = T2.CO_ARUB

		And T2.CO_EMPR = T3.CO_EMPR
		And T2.CO_ARUB = T3.CO_ARUB
		And T2.CO_RUBR = T3.CO_RUBR

		And T3.CO_EMPR = T4.CO_EMPR
		And T4.NU_ANNO = @INNU_ANIO
		And T4.NU_MESE = @INNU_MESE
		And T3.CO_SUBR = T4.CO_SUBR
---		And T4.FE_PROY_EJEC >= @VDFE_INIC
		And DATEDIFF ( MONTH, @VDFE_INIC, T4.FE_DETA_DIGI ) <= 6  
---		And T4.TI_REPO = 'F'
		And T4.IM_DIGI != 0
	Group By	T1.CO_EMPR, T1.CO_ARUB, T2.CO_RUBR, T3.CO_SUBR
	Order By	T1.CO_EMPR, Max(T3.NU_ORDE)

/****************************************************/

SELECT	T1.CO_EMPR, MAX(T2.DE_NOMB),
		T1.CO_ARUB, MAX(T1.DE_ARUB), 
		T1.CO_RUBR, MAX(T1.DE_RUBR), T1.CO_SUBR, MAX(T1.DE_SUBR),
SUM(ISNULL(T1.IM_0001_NACI,0))*@VNNU_FACT, SUM(ISNULL(T1.IM_0002_NACI,0))*@VNNU_FACT, 
SUM(ISNULL(T1.IM_0003_NACI,0))*@VNNU_FACT, SUM(ISNULL(T1.IM_0004_NACI,0))*@VNNU_FACT, 
SUM(ISNULL(T1.IM_0005_NACI,0))*@VNNU_FACT, SUM(ISNULL(T1.IM_0006_NACI,0))*@VNNU_FACT,
--SUM(ISNULL(T1.IM_0007_NACI,0))*@VNNU_FACT, 
SUM(ISNULL(T1.IM_0001_EXTR,0))*@VNNU_FACT, SUM(ISNULL(T1.IM_0002_EXTR,0))*@VNNU_FACT, 
SUM(ISNULL(T1.IM_0003_EXTR,0))*@VNNU_FACT, SUM(ISNULL(T1.IM_0004_EXTR,0))*@VNNU_FACT, 
SUM(ISNULL(T1.IM_0005_EXTR,0))*@VNNU_FACT, SUM(ISNULL(T1.IM_0006_EXTR,0))*@VNNU_FACT
--SUM(ISNULL(T1.IM_0007_EXTR,0))*@VNNU_FACT
FROM #TWFLUJ_CAJA_Q02_1 T1, TMEMPR T2
WHERE T1.CO_EMPR = T2.CO_EMPR
GROUP BY T1.CO_EMPR, T1.CO_ARUB, T1.CO_RUBR, T1.CO_SUBR

RETURN
/********************************* FIN ***********************************/


GO
